﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OleDb;
using Maticsoft.DBUtility;
using System.Collections;

namespace SkWeb.RiskDB
{
    public partial class FXCX : System.Web.UI.Page
    {
        static  Dictionary<string, string> dic_col_par = new Dictionary<string, string>();
        static Dictionary<string, bool> dic_type_par = new Dictionary<string, bool>();  
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                this.FileUploader1.FileReceived += new EventHandler<com.flajaxian.FileReceivedEventArgs>(FileUploader1_FileReceived);
                if (dic_col_par.Count == 0)
                {
                    dic_col_par.Add("厂别","cb");
                    dic_col_par.Add("井号", "jh");
                    dic_col_par.Add("区块", "qk");
                    dic_col_par.Add("传输方式", "csfs");
                    dic_col_par.Add("溢流", "yl");
                    dic_col_par.Add("井涌", "jy");
                    dic_col_par.Add("井喷", "jp");
                    dic_col_par.Add("油底", "yd");
                    dic_col_par.Add("油顶", "ydg");
                    dic_col_par.Add("地层压力Mpa", "dcyl");
                    dic_col_par.Add("地层压力", "dcyl");
                    dic_col_par.Add("层位", "cw");
                    dic_col_par.Add("有效厚度", "yxhd");
                    dic_col_par.Add("有效渗透率", "yxstl");
                    dic_col_par.Add("完井液密度", "wjymd");
                  
                }

                if (dic_type_par.Count == 0)
                {
                    dic_type_par.Add("厂别", true);
                    dic_type_par.Add("井号", true);
                    dic_type_par.Add("区块", true);
                    dic_type_par.Add("传输方式", true);
                    dic_type_par.Add("溢流", false);
                    dic_type_par.Add("井涌", false);
                    dic_type_par.Add("井喷", false);
                    dic_type_par.Add("油底", false);
                    dic_type_par.Add("油顶", false);
                    dic_type_par.Add("地层压力Mpa", false);
                    dic_type_par.Add("地层压力", false);
                    dic_type_par.Add("层位", true);
                    dic_type_par.Add("有效厚度", false);
                    dic_type_par.Add("有效渗透率", false);
                    dic_type_par.Add("完井液密度", false);
                }

            }
        }

        void FileUploader1_FileReceived(object sender, com.flajaxian.FileReceivedEventArgs e)
        {
            if (this.FileUploader1.FileIsPosted)
            {

                   List<DataTable> inputdt = new List<DataTable>();//e.File.FileName
                    int len = e.File.FileName.ToString().Trim().Length;
                    string path = "~/FileUpLoad/" + e.File.FileName.ToString().Trim();
                    path = Server.MapPath(path);
                    inputdt = InputExcel(path);
                    ArrayList strSqls = new ArrayList();
                    foreach (DataTable dt in inputdt)
                    {
                        string strCol = " ( ";
                        foreach(DataColumn dc in dt.Columns)
                        {
                            if(dc.Caption.ToString().IndexOf('F')==-1)
                            strCol += dic_col_par[dc.Caption] + ",";
                        }
                        strCol = strCol.Substring(0, strCol.Length - 1);
                        strCol += ") ";
                        string strSql = "insert into tb_exception_info ";
                        strSql += strCol;
                        foreach (DataRow dr in dt.Rows)
                        {
                            string strVal = " values( ";
                            foreach (DataColumn dc in dt.Columns)
                            {
                                if (dc.Caption.ToString().IndexOf('F') == -1)
                                {
                                    if (dic_type_par[dc.Caption])
                                    {
                                        strVal += "'" + dr[dc.Caption].ToString() + "',";
                                    }
                                    else
                                    {
                                        if (System.Convert.IsDBNull( dr[dc.Caption]))
                                            strVal += "0,";
                                        else
                                        {
                                            if (dr[dc.Caption].ToString().Trim().Length<1)
                                                strVal += "0,";
                                            else
                                                strVal += dr[dc.Caption].ToString()+",";
                                        }
                                    }
                                }
                            }
                            strVal = strVal.Substring(0, strVal.Length - 1);
                            strVal += ")";
                            strSqls.Add(strSql + strVal);
                        }
                    }

                    try
                    {
                        OracleHelper.ExecuteSqlTran(strSqls);
                        Response.Write("<script>alert('文件导入成功')</script>");
                        Response.End();
        
                    }
                    catch (Exception ex)
                    {
                        string str = ex.Message;
                    }
            }
            else
                throw new Exception("请选择导入表的路径");

        }


        /// <summary>
        /// 导入数据到数据集中
        /// </summary>
        /// <param name="Path"></param>
        /// <param name="TableName"></param>
        /// <param name="tablename2">如果这个有就以他为表名，没有的话就以TableName</param>
        /// <returns></returns>
        public List<DataTable> InputExcel(string Path)
        {
            try
            {
                List<DataTable> tables = new List<DataTable>();
                string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
                OleDbConnection conn = new OleDbConnection(strConn);
                conn.Open();

                System.Data.DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                foreach (DataRow row in dt.Rows)
                {
                    string TableName= row["TABLE_NAME"].ToString(); //就是
                    string strExcel = "select * from [" + TableName + "]";
                    OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcel, strConn);
                    DataTable dt1 = new DataTable();
                    myCommand.Fill(dt1);
                    tables.Add(dt1);

                }
                conn.Close();
                return tables;
             
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }


    }
}